package qwqw;
import java.sql.Connection;
import java.sql.SQLException;

import Entity.*;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import Entity.ReqForm;



public class mysqlConnection {
	
	
	private Statement stmt;
	private Statement stmt_mail; 
	private ResultSet rs = null;
	private Object result;
	
	private Connection conn;
	
	
	public mysqlConnection(Statement s){
		this.stmt_mail=s;
	}
	public mysqlConnection()
	{
		try 
		{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {/* handle the error*/}
        
        try 
        {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/icmproject","root","12345");
            System.out.println("SQL connection succeed");
     	} catch (SQLException ex) 
     	    {/* handle any errors*/
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            }
        try{
        	stmt = conn.createStatement();
        }catch(Exception ex){}
        }
	/**
	 * This method will add new request to the database
	 * @param req will contain request's details
	 * @return Integer that will comtain the request's number
	 */
	
	public Object addRequest(Request req){
		

		try {
			stmt=conn.createStatement();
			stmt.executeUpdate("INSERT INTO request (`in_system`,  `change_req`,`current_status`,`argument`,`comments`,`attached_files_path` ,`initiator`, `date_req`) " + " " +
					" VALUES ('"+req.getForm().getIn_system()+"', '"+req.getForm().getChange_request()+"','"+req.getForm().getCurrent_status()+"','"+req.getForm().getArgument()+"','"+req.getForm().getComments()+"', '"+req.getForm().getAttach_files_path()+"','"+req.getInitiator()+"' ,current_date())");
		
			
	       Integer req_num=new Integer((Integer) this.getReq_number(req.getForm()));
	   	   stmt.executeUpdate("insert into userinreq (r_id,userName,autoNum) values ('"+req_num+"','"+req.getInitiator()+"',1)");
	    	rs=stmt.executeQuery("select user_name,role from user where role=5 or role=6 or role=7 or role=8");
	    	while (rs.next()){
	    		stmt.executeUpdate("insert into userinreq (r_id,userName,autoNum) values ('"+req_num+"','"+rs.getInt("user_name")+"','"+rs.getInt("role")+"')");	
	    	}
	    	
		result=req_num;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
		}
	
	/**
	 * this method find the role of the user
	 * @param user contain details of the user
	 * @return user with his role
	 */
	
	public Object get_role(User user){
		try {
			rs=stmt.executeQuery("select role from user where user_name='"+user.getUserName()+"' ");
		    if(rs.next()){
		    	result =  rs.getString("role");
		    }
		    else 
		    	result=null;
		
		
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
		
	}
		
	


/**
	 * This method will get the request id of a request
	 * @param form will contain the request's form and by this parameters the method finds
	 * the request's id
	 * @return 
	 */
	
	public Object getReq_number(ReqForm form){
		
		
		try {
		
			 rs = stmt.executeQuery("SELECT r_id FROM request WHERE in_system='"+form.getIn_system()+"'AND current_status ='"+form.getCurrent_status()+"'AND  change_req='"+form.getChange_request()+"'AND  date_req=CURRENT_DATE()");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();}
			try {
				if (rs.next()){
					
				Integer	result_in=new Integer(rs.getInt("r_id"));
				result=result_in;}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
				return result;}
	/**
	 * This method checks weather the user exist in the data base or not
	 * @param user contains user and password of the user that is trying to login
	 * @return  true if the user exist and false otherwise
	 */
	public Object loggin_check(User user){
	

		try {

             rs = stmt.executeQuery("SELECT user_name,password, role FROM user WHERE user_name='"+user.getUserName()+"'AND password ='"+user.getPassword()+"'");

			//stmt=conn.createStatement();
			
			 //rs = stmt.executeQuery("SELECT Email FROM user WHERE user_name='"+user.getUserName()+"'AND password ='"+user.getPassword()+"'");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();}
			try {
				if (rs.next()){
					Boolean result_bool=new Boolean("true");
					//user.setRole(rs.getString("role"));
					result=result_bool;	
					}
					else{
						Boolean result_bool=new Boolean("false");
						result=result_bool;
					}
					
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
				return result;

		}


			
			

	/**This method change the given request status to "frozen"
	 * 
	 * @param req_id is the request's id that will be frozen
	 */
	public Object changeToFrozenQuery(int req_id){
		
		try{
			PreparedStatement preparedStatement = conn.prepareStatement("insert into request_status (r_id , status , reqDate) values(?,?, current_date)");
			preparedStatement.setInt(2, req_id);
			preparedStatement.setString(2, "FROZEN");
			Integer in=new Integer(preparedStatement.executeUpdate());
			result=in;
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
		
	}
	
	/**
	 * This method check if the given stage exists and open
	 * @param: r_id is the request id and s_id is the stage id
	 * @return true if stage exists and open and false if not exists or closed
	 */
	public Object stageIsOpenQuery(int r_id, int s_id){
		PreparedStatement preparedStatement;
		try {
			preparedStatement = conn.prepareStatement("select * from stageinreq where r_id=? and stageID =? and openDate is not null and closureDate is null");
			preparedStatement.setInt(1,r_id);
			preparedStatement.setInt(2,s_id);
			rs = preparedStatement.executeQuery();
			if(rs.next())
				return (Object)true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();}
		return (Object)false;
		}
		
	/**
	 * This method first checking if the stage is open and if the related request is ACTIVE and then checking for a specific stage if it was extended before or the date of extension request 
	    is lass than 3 days from the finishing date, if all conditions are apply the enter a new line into TABLE  "extantionReq" and WAITING for the supervisor approval
	 * @param stg is the entity that contain all details of the stage we like to extend   
	 * @return true if was'nt extended before and the date of extension request 
	    is lass than 3 days from the finishing date, else return false
	 */
	public Object CheckExtentionStageQuery(Extention_Stage_req stg){
		String s = new String();
		Object obj;
		try{
			int stageId= getStageID(stg.getStageName());
			String ans1 =(String) this.getRequestStatus(stg.getR_id());
			Boolean ans2 = (Boolean)this.stageIsOpenQuery(stg.getR_id(), stageId);
			if(ans1.equals("ACTIVE") && ans2){
				s =  "SELECT status FROM extantionReq WHERE r_id = ? and stageId = ? ";
				PreparedStatement preparedStatement = conn.prepareStatement(s);
				preparedStatement.setInt(1,stg.getR_id());
				preparedStatement.setInt(2, stageId);
				rs = preparedStatement.executeQuery();
				if(rs.next()){
					if(rs.getString("status").equals("EXTENDED"))
						return (Object)false;
					else
						{
							s =  "SELECT duration, .openDate, CURDATE() as cur FROM stageInReq WHERE r_id = ? and stageId = ? ";
							preparedStatement = conn.prepareStatement(s);
							preparedStatement.setInt(1,stg.getR_id());
							preparedStatement.setInt(2, stageId);
							rs = preparedStatement.executeQuery();
						
							if(rs.next()){
								String oDay = new SimpleDateFormat("dd/MM/yyyy").format(new Date(rs.getTimestamp("openDate").getDay())); 
								String cDay = new SimpleDateFormat("dd/MM/yyyy").format(new Date(rs.getTimestamp("CURDATE()").getDay())); 
								if((Integer.parseInt(oDay)+ rs.getInt("duration")-Integer.parseInt(cDay))<=3 )
									return (Object)false;
							else 
							{
								s="insert into extantionReq (r_id,stageID,arrgumant,status,numDay,date) values (?,?,?,?,?,current_timestamp())";
								preparedStatement = conn.prepareStatement(s);
								preparedStatement.setInt(1, stg.getR_id());
								preparedStatement.setInt(2, stageId);
								preparedStatement.setString(3, stg.getArrgumant());
								preparedStatement.setString(4, "WAITING");
								preparedStatement.setInt(5, stg.getNumDay());
								rs = preparedStatement.executeQuery();
								return (Object)true;
							}
						}
			
					}
				}
				
				rs.close();
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return (Object)false;
	}

	/**
	 * This method open a new stage and before that check if this stage is really exist  
	 * @param newStage is the new stage  
	 */
	public void insertNewStageQuery(Stage newStage){
		try{
			int stageID = getStageID(newStage.getStageName());
			rs = stmt.executeQuery("SELECT * FROM stageInReq WHERE stageID="+stageID +"AND r_id=" + newStage.getRequestID());
			if(rs.next())
				stmt.executeUpdate("UPDATE stageInReq SET openDate = current_timestamp() WHERE r_id=" + newStage.getRequestID() + " and stageID= "+ stageID);
			rs.close();
		}catch (SQLException e) {e.printStackTrace();}
	}
	/**
	 * This method check if satge is allrdy opened and if it doe's, initialize the stage's duration
	 * @param stageManager is an array witch contain requestId,stageID and stage's duration 
	 */
	public Object insertStageDurationQuery(Stage stage){
		try{
			int stageID = getStageID(stage.getStageName());
			rs = stmt.executeQuery("SELECT * FROM stageInReq WHERE stageID="+stageID +"AND r_id=" + stage.getRequestID());
			if(rs.next())
				stmt.executeUpdate("UPDATE stageInReq SET duration =" + stage.getDuration()+" WHERE r_id=" + stage.getRequestID() + " and stageID= "+ stageID);
			else return (Object)false;
			rs.close();
		}
		catch (SQLException e) {e.printStackTrace();}	
		return (Object)true;
	}

	
	/**
	 * This method create a new stage and initialize the stage's manager
	 * @param stageManager is an array witch contain requestId,stageID and stage's manager 
	 */
	public Object insertStageManagerQuery(int [] stageManager){
		try{	
			PreparedStatement preparedStatement = conn.prepareStatement("insert into stageInReq (r_id,stageID,stageManagerId) values(?,?,?)");
			preparedStatement.setInt(1, stageManager[0]);
			preparedStatement.setInt(2, stageManager[1]);
			preparedStatement.setInt(3, stageManager[2]);
			preparedStatement.executeUpdate();
		} 
		catch (SQLException e) {e.printStackTrace();}	
		return (Object)true;
	}
	
/**
 * this method will find the current stage of a specific request
 * @param req_num is the request number of the request
 * @return Array that contain the parameters:current stage name and expected_close stage date
 */
	public Object viewReq(int req_num){
		try {
			stmt=conn.createStatement();
			stmt.executeUpdate("drop view reqStage");
			stmt.executeUpdate("CREATE VIEW reqStage AS select openDate from stageinreq where r_id='"+req_num+"'");
			                      
			rs=stmt.executeQuery("select stageID,DATE(openDate+ INTERVAL  duration DAY) as expected_close from stageinreq where r_id='"+req_num+"' and openDate=(select MAX(openDate) from( reqStage ))");
			
			if (rs.next()){
				Stage stage=new Stage();
			int stage_id=rs.getInt("stageID");
			//	stage.setExpected_closure_date(rs.getDate("expected_close"));
				
			rs=null;
			rs=stmt.executeQuery("select stageName from stage where stageID ='"+stage_id+"'");
			if(rs.next()){
			   stage.setStageName(rs.getString("stageName"));}
			   result=stage;
			
			
			
		}} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;}
	

	/**
	 * this method will "unfreeze" frozen request and update the correct duration for 
	 * the last stage the request was in
	 * @param req_id is the number of the request 
	 * @return stage that will contain the cuurent stage,the updated duration for the stage
	 */
	
	public Object unfreezReq(int req_id){
		
		try{
			stmt=conn.createStatement();
		
			Stage stage=new Stage();
			stage=(Stage) this.viewReq(req_id);
			stmt.executeUpdate("drop view dates");
			stmt.executeUpdate("CREATE VIEW dates AS select reqDate from requeststatus where reqStatus='FROZEN' and r_id='"+req_id+"'");
			rs=stmt.executeQuery("select DATE(reqDate) as freeze_date from requeststatus where r_id='"+req_id+"'  and reqDate=(select MAX(reqDate) from( dates ))");
			
			if (rs.next()){
				/*
				 * calculate (duration-(freeze date-open_stage date)+(today-freeze date)) in order to update the correct duration
				 */
				
				Date freezeDate=rs.getDate("freeze_date");//date of freezing request
				Date openDtae=stage.getStartDate();//date of open current stage
			    long day_in_millis = 1000 * 60 * 60 * 24;
				int days_between_open_to_freeze = (int) ((freezeDate.getTime() - openDtae.getTime())/ day_in_millis );
				Date today = new Date(System.currentTimeMillis());
				System.out.println("difference"+days_between_open_to_freeze);
				int days_between_today_to_freeze = (int) ((today.getTime() - freezeDate.getTime())/ day_in_millis );
                stage.setDuration(stage.getDuration()-days_between_open_to_freeze+days_between_today_to_freeze);
			    result=stage;
			    //update request to "ACTIVE"
			    stmt.executeUpdate("INSERT INTO requeststatus (`r_id`, `reqStatus`,`reqDate`) " + " " +
						" VALUES ('"+req_id+"', 'ACTIVE',current_timestamp())");
	                   
			    
               
				
			}}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return result ;}
			
		


	/**
	 * This method get all the "WAITING" extension stage's requests 
	 * @return a list of all  "WAITING" extension stage's requests
	 */
	
	public Object getWaitingReqQuery(){

		try{
		ArrayList<Extention_Stage_req> waitList = new ArrayList<Extention_Stage_req>();
		PreparedStatement preparedStatement = conn.prepareStatement("select r_id,stageName,arrgumant,numDay,date from extantionreq,stage where status = ? and extantionreq.stageID=stage.stageID ");
		preparedStatement.setString(1, "WAITING");
		rs = preparedStatement.executeQuery();
		while(rs.next()){
			Extention_Stage_req esr= new Extention_Stage_req();
			esr.setR_id(rs.getInt("r_id"));
			esr.setStageName(rs.getString("stageName"));
			esr.setArrgumant(rs.getString("arrgumant"));
			esr.setNumDay(rs.getInt("numDay"));
			//esr.setTimestamp(rs.getTimestamp("date"));
			waitList.add(esr);
		}
		result = waitList; 
		} 
		catch (SQLException e) {e.printStackTrace();}
		return result;
	}
	/**
	 * this method update stage status to accept OR declined by the supervisor diction
	 * @param esr contain the  extension stage details
	 * @param ans contain the supervisor diction
	 */
	public Object acceptORdeclinedQuery(Extention_Stage_req esr){
		try{
			stmt.executeUpdate("UPDATE extantionreq SET status =\""+esr.getStatus()+"\""+ "where r_id="+ esr.getR_id()+ "and status = \"WAITING\" and stageId =(select stageID from stage where stageName = \""+esr.getStageName()+"\"");
			stmt.executeUpdate("UPDATE stageInReq SET duration=duration+ '"+esr.getNumDay()+"' where r_id= '"+esr.getR_id()+ "' and stageId =(select stageID from stage where stageName = \""+esr.getStageName()+"\"and closureDate is null");
			return (Object)true;
		}catch (SQLException e) {e.printStackTrace();}
		return (Object)false;
		
	}
	/**
	 * This method determine the supervisor or the chairman by the chifDep 
	 * @param user us the entity we would like to determined
	 * @return true if there is no supervisor or chairman already and the appointment successes and false otherwise
	 */
	public Object determineSupervisorORchairmanQuery(User user){
		if(!(this.checkUserExsistsQuery(user).equals(""))){
			try{
				PreparedStatement preparedStatement = conn.prepareStatement("select * from user where role = ?");
				preparedStatement.setString(1, user.getRole());
				rs = preparedStatement.executeQuery();
				if(rs.next())
					return ((Object)false);
				else{
					stmt.executeUpdate("UPDATE user SET role =\""+user.getRole()+"\""+ "where user_name="+ user.getUserName());
					return (Object)true;
				}
			}catch (SQLException e) {e.printStackTrace();}
		}
		return (Object)false;
	}
	/**
	 * This method update the supervisor or the chairman by the chifDep 
	 * @param user us the entity we would like to update his role
	 * @return false if there is no supervisor or chairman already and true otherwise
	 */
	public Object updateSupervisorORchairmanQuery(User user){
		if(!(this.checkUserExsistsQuery(user).equals(""))){
			try{
				PreparedStatement preparedStatement = conn.prepareStatement("select * from user where role = ?");
				preparedStatement.setString(1, user.getRole());
				rs = preparedStatement.executeQuery();
				if(rs.next()){
					stmt.executeUpdate("UPDATE user SET role =\"null\" where user_name="+ rs.getInt("user_name"));
					stmt.executeUpdate("UPDATE user SET role =\""+user.getRole()+"\""+ "where user_name="+ user.getUserName());
					return ((Object)true);
					}
				}catch (SQLException e) {e.printStackTrace();}
		}
		return ((Object)false);
	}
	/**
	 * this method get the stage ID from the DB
	 * @param s_name is the stage name
	 * @return the stage ID related to the stage name
	 */
	public int getStageID(String s_name){
		PreparedStatement preparedStatement;
		try {
			String selectSQL = "SELECT * FROM stage WHERE stageName = ?";
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setString(1, s_name);
			rs = preparedStatement.executeQuery();
			if(rs.next()){
				return  rs.getInt("stageID"); 
			}
		}catch (SQLException e) {}
		return 0;
	}
	/**
	 * this method get the arguments and number of extended stage request
	 * @param exSr contain the details of stage id and request id
	 * @return exSr with the number of days extension and the arguments of the stage request extension
	 */
	public Object getExtendedReq(Extention_Stage_req exSr){
		PreparedStatement preparedStatement;
		try {
			int stageID = getStageID(exSr.getStageName());
			preparedStatement = conn.prepareStatement("select arrgumant,numDay from extantionreq where r_id=? and stageID=? and status=?");
			preparedStatement.setInt(1,exSr.getR_id());
			preparedStatement.setInt(2, stageID);
			preparedStatement.setString(3, "WAITING");
			rs = preparedStatement.executeQuery();	
			exSr.setArrgumant(rs.getString("arrgumant"));
			exSr.setNumDay(rs.getInt("numDay"));
		} catch (SQLException e) {}
		return (Object)exSr;	
	}
	/**this method change the stage  from 
	 * 
	 * @return
	 */
	public Object changeStageStatus(Extention_Stage_req exsR){
		try {
			int stageName = getStageID(exsR.getStageName());
			stmt.executeUpdate("UPDATE extantionreq SET status =\""+exsR.getStatus()+"\"where r_id=" + exsR.getR_id());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return (Object)true;
		
		
	}
	/**
	 * this method check if user exists in DB 
	 * @param user details
	 * @return the name (private & last name) of the user
	 */
	public Object checkUserExsistsQuery(User user){
		PreparedStatement preparedStatement;
		String userName="";
		try {
			preparedStatement = conn.prepareStatement("select name from user where user_name = ?");
			preparedStatement.setInt(1, user.getUserName());
			rs = preparedStatement.executeQuery();
			userName = rs.getString("name");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (Object)userName;
	}
	/**
	 * This method get the EMAIL of a given user 
	 *  @param user contain the user details
	 *  @return mail is the Email of the user 
	 */
	public Object getmailQuery(User user){
		PreparedStatement preparedStatement;
		String mail="";
		try {
			if(!(this.checkUserExsistsQuery(user).equals(""))){
				preparedStatement = conn.prepareStatement("select Email from user where user_name = ?");
				preparedStatement.setInt(1, user.getUserName());
				rs = preparedStatement.executeQuery();
				mail = rs.getString("Email");
				}
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (Object)mail;	
	}
	public  Object setfailureReportQuery(Failure_report fr){
		PreparedStatement preparedStatement;
		try{	
			preparedStatement = conn.prepareStatement("insert into failurereport values(?,?,current_date,?,?)");
			preparedStatement.setInt(1,fr.getReqID());
			preparedStatement.setInt(2, fr.getTesterID());
			preparedStatement.setString(3,fr.getDecision());
			preparedStatement.setString(4,fr.getComments());
			preparedStatement.executeUpdate();
			
		} 
		catch (SQLException e) {e.printStackTrace(); return (Object)false;}	
		return (Object)true;
	}
	/**
	 * This method insert the data of the estimation report entered by the estimator
	 * @param the estimation report
	 * @return true is the saving completed and false otherwise
	 */
	public  Object setEstimationReportQuery(EstimationReport esr){
		PreparedStatement preparedStatement;
		try{	
			preparedStatement = conn.prepareStatement("insert into estimationreport values(?,?,?,?,?,?,?,?)");
			preparedStatement.setInt(1,esr.getR_id());
			preparedStatement.setInt(2, esr.getEstimationID());
			preparedStatement.setString(3,esr.getSystemInformation());
			preparedStatement.setString(4,esr.getChangeRequired());
			preparedStatement.setString(5,esr.getBenefit());
			preparedStatement.setString(6,esr.getRisks());
			preparedStatement.setInt(7,esr.getDurationEstimation());
			preparedStatement.setString(5,esr.getConstraints());
			preparedStatement.executeUpdate();
			
		} 
		catch (SQLException e) {e.printStackTrace(); return (Object)false;}	
		return (Object)true;
	}
	/**
	 * This method update the committee member by the chifDep and before check if exist in DB tow  committee member to able update
	 * @param user us the entity we would like to update his role
	 * @return false if there is committee member already exist or if there are no 2 members and true otherwise
	 */
	public Object updateCommitteeMember(User user){
		if(!(this.checkUserExsistsQuery(user).equals(""))){
			try{
				PreparedStatement preparedStatement = conn.prepareStatement("select role, COUNT(user_name) as count from user where role = ? group by role having count>=1");
				preparedStatement.setString(1, user.getRole());
				rs = preparedStatement.executeQuery();
				if(rs.next()) 
				{
					preparedStatement = conn.prepareStatement("select role,user_name from user where role = ?");
					preparedStatement.setString(1, user.getRole());
					rs = preparedStatement.executeQuery();
					if(rs.next()){
						if(rs.getInt("user_name")==user.getUserName())
							return ((Object)false);
						else{
							stmt.executeUpdate("UPDATE user SET role =\""+user.getRole()+"\""+ "where user_name="+ user.getUserName());	
							return  ((Object)true);
							}
					}
				}
			}catch (SQLException e) {e.printStackTrace();}
		}
		return ((Object)false);
	}
	/**
	 * This method determined the committee member by the chifDep and before check if exist in DB tow  committee member to able determine a new one
	 * @param user is the entity we would like to update his role and determine him to be the committee member
	 * @return false if there is committee member already exist or if there are more then 2 members and true otherwise
	 */
	public Object determineCommitteeMember(User user){
		if(!(this.checkUserExsistsQuery(user).equals(""))){
			try{
				PreparedStatement preparedStatement = conn.prepareStatement("select role, COUNT(user_name) as count from user where role = ? group by role having count>=1");
				preparedStatement.setString(1, user.getRole());
				rs = preparedStatement.executeQuery();
				if(rs.next()) 
					return ((Object)false);
				else{
					preparedStatement = conn.prepareStatement("select role,user_name from user where role = ?");
					preparedStatement.setString(1, user.getRole());
					rs = preparedStatement.executeQuery();
					if(rs.next()){
						if(rs.getInt("user_name")==user.getUserName())
							return ((Object)false);
						else
							stmt.executeUpdate("UPDATE user SET role =\""+user.getRole()+"\""+ "where user_name="+ user.getUserName());	
						}
				}
			}catch (SQLException e) {e.printStackTrace();}
		}
		return ((Object)true);
	}
	/**
	 * This method get all the request per status and count the total duration
	 * @param acR is the entity the contain the started and finishing date for running the report
	 * @return
	 */
	public Object getActivityReportQuery(ActivityReport acR){
		try{

			stmt.executeUpdate("CREATE VIEW help AS select r_id, reqStatus, DATE(reqDate)as date  from requeststatus");
			PreparedStatement preparedStatement = conn.prepareStatement("select reqStatus, date, COUNT(DISTINCT r_id) as countReq from help where date >= \""+acR.getFrom()+"\" and date<= \""+acR.getTo()+"\" group by reqStatus, date ORDER BY date ");
			rs = preparedStatement.executeQuery();
			while(rs.next()){
				if(rs.getString("reqStatus").equals("ACTIVE")){
					acR.setActiveReqList(rs.getInt("countReq"));
					acR.setClosedReqList(0);
					acR.setRejectReqList(0);
					acR.setFrozenReqList(0);
					System.out.println("1");
					//acR.setActiveReqDuration(rs.getInt("date"));
				}
				else if(rs.getString("reqStatus").equals("CLOSED")){
					acR.setActiveReqList(0);
					acR.setClosedReqList(rs.getInt("countReq"));
					acR.setRejectReqList(0);
					acR.setFrozenReqList(0);
					//acR.setActiveReqDuration(rs.getInt("date"));
				}
				else if(rs.getString("reqStatus").equals("REJECTED")){
					acR.setActiveReqList(0);
					acR.setClosedReqList(0);
					acR.setRejectReqList(rs.getInt("countReq"));
					acR.setFrozenReqList(0);
					//acR.setActiveReqDuration(rs.getInt("date"));
				}
				else if(rs.getString("reqStatus").equals("FROZEN")){
					acR.setActiveReqList(0);
					acR.setClosedReqList(0);
					acR.setRejectReqList(0);
					acR.setFrozenReqList(rs.getInt("countReq"));
					//acR.setActiveReqDuration(rs.getInt("date"));
				}	
			}
			preparedStatement = conn.prepareStatement("select reqStatus, requestStatus.r_id, sum(duration) as countDur from requestStatus, stageInReq where requestStatus.r_id = stageInReq.r_id  group by requestStatus.reqStatus, requestStatus.r_id ");
			rs = preparedStatement.executeQuery();
			while(rs.next()){
				if(rs.getString("reqStatus").equals("ACTIVE")){
					acR.setActiveReqDuration(rs.getInt("countDur"));
					System.out.println("2");
				}
				else if(rs.getString("reqStatus").equals("CLOSED")){
					acR.setClosedReqDuration(rs.getInt("countDur"));
				}
				else if(rs.getString("reqStatus").equals("REJECTED")){
					acR.setRejectReqDuration(rs.getInt("countDur"));
				}
				else if(rs.getString("reqStatus").equals("FROZEN")){
					acR.setFrozenReqDuration(rs.getInt("countReq"));
				}	
			}
			stmt.executeUpdate("drop view help");			
		}catch(SQLException e) {e.printStackTrace();}
		return acR;		
	}
	/**
	 * This method get the sum of durations of all the the approved extended request and the sum of all the returned stage's duration 
	 * @param par is the entity that contains the period of time to run the report on
	 * @return the entity with all data
	 */
	public Object getParformanceReportQuery(Parformance_report par){
		try {
			String s = "EXTENDED";
			stmt.executeUpdate("CREATE VIEW helps AS select status, numDay, DATE(date) as date  from extantionreq");
			PreparedStatement preparedStatement = conn.prepareStatement("select sum(numDay) as count from helps where status =\""+s+"\""+" and date>=\""+par.getFrom()+"\""+" and date<=\""+par.getTo()+"\"" );
			rs = preparedStatement.executeQuery();
			par.setSumExtendedApp(rs.getInt("count"));
			stmt.executeUpdate("drop view helps");
		} catch (SQLException e) {e.printStackTrace();}
		
		try {
			stmt.executeUpdate("create view temp as select r_id,stageID,openDate from stageinreq where (stageID='2' or stageID= '4') and  closureDate<> 'NULL'");
			stmt.executeUpdate("create view temp1 as (select r_id,stageID, MIN(openDate) as mini  from stageinreq where (stageID='2' or stageID= '4') and closureDate<> 'NULL' group by  r_id,stageID)");
			stmt.executeUpdate("create view temp2 as (select r_id,stageID,openDate from temp where openDate not in (select mini from temp1))");
			rs = stmt.executeQuery("select SUM(duration) as sum from stageinreq, temp2 where stageinreq.openDate=temp2.openDate group by temp2.r_id");
			if(rs.next())		
				par.setSumReturnStageDur(rs.getInt("sum"));
		} catch (SQLException e) {e.printStackTrace();}
		
		return par;

		
		
	}
	/**
	 * this method will calculate the number and sum of all late requests
	 * @param t_rep contain the stars and end day to generate the report  
	 * @return tardiness_repord that contain thr information to generate the tardiness report
	 */
	
	public Object tardiness_report (Tardiness_report t_rep){
	
	String from=t_rep.getFrom();
	String to=t_rep.getTo();
	
	try {
		

// creating view that will contain expected closure date and the closure date
		stmt.executeUpdate("drop view dates");
		stmt.executeUpdate("create view dates as SELECT DATE(openDate+ INTERVAL  duration DAY )as new_date,closureDate,r_id,stageID from stageinreq");
// create view with all the late requests		
		stmt.executeUpdate("drop view late_req");
		stmt.executeUpdate("create view late_req as select r_id,stageID,new_date,closureDate from dates where((new_date < current_date() and closureDate is null)or (new_date < closureDate and closureDate is not null) )and  (new_date >=  \""+from+"\"and new_date <=  \""+to+"\")");
// create view of request_id, the latest date we updated new status		
		stmt.executeUpdate("drop view req_max_dates");
		stmt.executeUpdate("create view req_max_dates as select r_id,max(reqDate) as max_date from requeststatus group by r_id");
// create view with all the current frozen requests		
		stmt.executeUpdate("drop view frozen_req");
		stmt.executeUpdate("create view frozen_req as select requeststatus.r_id from requeststatus,req_max_dates where requeststatus.r_id=req_max_dates.r_id and requeststatus.reqStatus='FROZEN' and requeststatus.reqDate=req_max_dates.max_date");
// create view with all the delay requests and the number of the dayes in late
		stmt.executeUpdate("drop view late_req_true");
// union between requests that is closed ond open		
		stmt.executeUpdate("create view late_req_true as select late_req.r_id as r_id,late_req.stageID as stage_id,late_req.closureDate-late_req.new_date as late_dur,request.in_system as in_sys from late_req,request where late_req.r_id=request.r_id and late_req.r_id not in(select* from frozen_req) and late_req.closureDate is not null"+" "+		
				           "union"+" "+
				            "select late_req.r_id as r_id,late_req.stageID as stage_id,current_date()-late_req.new_date as late_dur,request.in_system as in_sys	from late_req,request where late_req.r_id=request.r_id and late_req.r_id not in(select* from frozen_req) and late_req.closureDate is null");
		
// sr will contain for each information system the number of lates and the sum of all lates
		rs=stmt.executeQuery("select sum(late_dur) as sum,count(*)as count,in_sys from late_req_true group by in_sys");
		
	  while (rs.next()){
		  t_rep.getNumber_late_requests().add(rs.getInt("count"));
		  t_rep.getSum_late_requests().add(rs.getInt("sum"));
		  Tardiness_report_in_array t_in_arr=new Tardiness_report_in_array();  
		  t_in_arr.setNumber_of_lates(rs.getInt("count"));
		  t_in_arr.setSum_of_lates(rs.getInt("sum"));
		  t_in_arr.setSystem_information(rs.getString("in_sys"));
		  t_rep.getArray_to_display().add(t_in_arr) ; 
		     
          result=t_rep;
	  }}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} return result;
	} 

	
	/**
	 * this method will update the status of the request to "CLOSED"
	 * @param req is the number of the request
	 * @return Integer:1-the update sucseede,0- otherwise
	 */
	public Object close_req(int req){
		try {
			
			int bool=stmt.executeUpdate("INSERT INTO requeststatus (`r_id`,  `reqStatus`,`reqDate`) " + " " +
					" VALUES ('"+req+"','CLOSED',current_timestamp)");
			Integer int_bool=new Integer(bool);
			result=int_bool;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return result;
		
	}
	/**
	 *  this method will update the status of the request to "ACTIVE"
	 * @param req is the number of the request
	 * @return Integer:1-the update sucseede,0- otherwise
	 */
	public Object active_req(int req){
		try {
			
			int bool=stmt.executeUpdate("INSERT INTO requeststatus (`r_id`,  `reqStatus`,`reqDate`) " + " " +
					" VALUES ('"+req+"','ACTIVE',current_timestamp)");
			Integer int_bool=new Integer(bool);
			result=int_bool;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return result;
		
	}
/**
 * this method will check the status of specific request
 * @param req_id is the number of the request.
 * @return the current status of the request:frozen/active/closed/unfrozen/rejected
 */

public Object getRequestStatus(int req_id){

		try {
		
			stmt.executeUpdate("drop view dates");
			stmt.executeUpdate("CREATE VIEW dates AS select reqDate from requeststatus where r_id='"+req_id+"'");
			rs=stmt.executeQuery("select reqStatus from requeststatus where r_id='"+req_id+"' and reqDate=(select MAX(reqDate) from( dates ))");
			
			if (rs.next()){
			String status=rs.getString("reqStatus");
			result=status;}	
		} 	
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
}
	/**
	 
	 * This method get all the list of authorization per request for a specific user
	 * @param id is the value of the user id that logged to the system
	 * @return reqId_auto is a container with contain requestId as the key and a list of 
	 		  user's authorization on this request as the value 
	 */
	public Object getPerrmisions(User user){
		String s = new String();
		ArrayList<Integer> autoList = new ArrayList<Integer>();
		//String[] reqList = new String [30];
		//int i=0;
		HashMap<Integer, ArrayList<Integer>> reqId_auto = new HashMap<Integer, ArrayList<Integer>>();
		//reqId_auto=null;
		try{
		s =  "SELECT r_Id, GROUP_CONCAT(autoNum) as autoNum FROM userinreq WHERE username = ? GROUP BY r_id ";
		PreparedStatement preparedStatement = conn.prepareStatement(s);
		preparedStatement.setInt(1, user.getUserName());
		ResultSet rs = preparedStatement.executeQuery();
		while(rs.next()){
			String [] tmp = rs.getString("autoNum").split(",");
				for (String x:tmp) {
					autoList.add(Integer.parseInt(x));
			}
		reqId_auto.put(rs.getInt("r_Id"), autoList);
		//reqList[i] = rs.getString("r_Id");
		//i++;
		}
		//user.setReq_list(reqList);
		user.setReq_auto_list(reqId_auto);
		user.setRole((String)get_role(user));
		rs.close();
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//user.setReq_auto_list(reqId_auto);
		return user;
	}
	/**
	 * this method check if the user exist is not in update the new user in the db
	 * @param user contains the user's details
	 * @return Boolean value-true if the the user eas updated in the db, false otherwise
	 */
	
	public Object add_new_user(User user){
		Boolean bool=new Boolean((Boolean) this.loggin_check(user));
		if (bool==true){
			bool=false;
			result=bool;
		}
		else {
			
				try {
					stmt=conn.createStatement();
				
				
				stmt.executeUpdate("INSERT INTO user (`username`,  `password`,`Email`,`role`) " + " " +
						" VALUES ('"+user.getUserName()+"', '"+user.getPassword()+"','"+user.getEmail()+"','"+user.getRole()+"')");
		       Boolean bool1=new Boolean("true");
		       result=bool1;
				}
				catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();}}
	
		return result;
	}
	
/**
 * this method will get all the frozen request today
 * @return array_list that will contain the stage in delay,number request,numbert of days in delay
 */
	public Object get_current_late_requests (){
	
	  ArrayList<Late_requests> late_requests = new ArrayList<Late_requests>();
	try {
		stmt=conn.createStatement();

// creating view that will contain expected closure date and the closure date
		stmt.executeUpdate("drop view dates");
		stmt.executeUpdate("create view dates as SELECT DATE(openDate+ INTERVAL  duration DAY )as new_date,closureDate,r_id,stageID from stageinreq");
// create view with all the late requests		
		stmt.executeUpdate("drop view late_req");
		stmt.executeUpdate("create view late_req as select r_id,stageID,new_date,closureDate from dates where new_date < current_date() and closureDate is null");
// create view of request_id, the latest date we updated new status		
		stmt.executeUpdate("drop view req_max_dates");
		stmt.executeUpdate("create view req_max_dates as select r_id,max(reqDate) as max_date from requeststatus group by r_id");
// create view with all the current frozen requests		
		stmt.executeUpdate("drop view frozen_req");
		stmt.executeUpdate("create view frozen_req as select requeststatus.r_id from requeststatus,req_max_dates where requeststatus.r_id=req_max_dates.r_id and requeststatus.reqStatus='FROZEN' and requeststatus.reqDate=req_max_dates.max_date");
// create view with all the delay requests and the number of the dayes in late
		
// select all the late requests without the frozen requests		
		rs=stmt.executeQuery( "select late_req.r_id as r_id,late_req.stageID as stage_id,current_date()-late_req.new_date as late_dur,request.in_system as in_sys	from late_req,request where late_req.r_id=request.r_id and late_req.r_id not in(select* from frozen_req) and late_req.closureDate is null");
		
// sr will contain for each information system the number of lates and the sum of all lates
		
	  while (rs.next()){
		  Late_requests l_t=new Late_requests();
		  l_t.setReq_id(rs.getInt("r_id"));
		  l_t.setLate_duration(rs.getInt("late_dur")) ;
		  l_t.setStage_id(rs.getInt("stage_id"));
		  late_requests.add(l_t);}
		  result=late_requests;}
	catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} return result;
	} 
	

	// set the new request in DB
	public void setQuery(ArrayList<String> msg){
		
		PreparedStatement preparedStatement;
		
		try{	
			preparedStatement = conn.prepareStatement("insert into request values(?,?,?)");
			preparedStatement.setInt(1, Integer.parseInt(msg.get(1)));
			preparedStatement.setInt(2, Integer.parseInt(msg.get(2)));
			preparedStatement.setString(3,msg.get(3));
			preparedStatement.executeUpdate();
		} 
		catch (SQLException e) {e.printStackTrace();}	
		
	}
	


}


